﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Import_PODetails : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        private DataTable ImportTable;
        
        private string error_text;

        private int index;
        private string order_prefix;

        private string supplier;
        private string deliveryterm;
        private string payment;

        
        public frm_Import_PODetails()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);

            this.index = 0;
            
            this.ImportTable = new DataTable();
            this.ImportTable.Columns.Add("OrderNo", typeof(string));
            this.ImportTable.Columns.Add("POID", typeof(string));
            this.ImportTable.Columns.Add("PartNo", typeof(string));            
            this.ImportTable.Columns.Add("Quantity", typeof(float));
            this.ImportTable.Columns.Add("UnitID", typeof(string));
            this.ImportTable.Columns.Add("UnitPrice", typeof(string));
            this.ImportTable.Columns.Add("CurrencyID", typeof(string));
            this.ImportTable.Columns.Add("DeliveryDate", typeof(DateTime));
            this.ImportTable.Columns.Add("TransportID", typeof(string));
            this.ImportTable.Columns.Add("DeliveryTermID", typeof(string));
            this.ImportTable.Columns.Add("Note", typeof(string));
            this.ImportTable.Columns.Add("Purpose", typeof(string));

            this.error_text = null;

            this.supplier = null;
            this.payment = null;
            this.deliveryterm = null;

            getDepartmentList();
            getPOIDList();            
        }

        ///////////////////////////////////////
        /// Import Order from Excel
        ///////////////////////////////////////
        private void ImportExcel()
        {
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();

            bool have_data = false;

            importFile.Filter = "Excel 2007-2013 (.xlsx)|*.xlsx";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {                
                // Import new data from Excel
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("Select [PartNo], [Quantity], [DeliveryDate], [TransportID],"
                        + "[Note], [Purpose] from [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);
                    have_data = true;
                    excelConnection.Close();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    MessageBox.Show("Get data error: " + ex.Message);
                    //MessageBox.Show("Cannot import data from file. Please check your file");
                }
            }

            if (have_data)
            {
                foreach (DataRow excel_row in excel_data.Rows)
                {
                    string partno = excel_row["PartNo"].ToString().Trim();
                    string unit = null;
                    float quantity = 0;
                    float price = 0;
                    string currency = null;
                    string err = null;
                    
                    string[] part_info = getPartNoInfo(partno);
                    
                    if (string.IsNullOrEmpty(part_info[0]))
                    {
                        error_text += partno + " : no quotation \r\n";
                    }
                    else
                    {
                        unit = part_info[1];
                        if (float.TryParse(excel_row["Quantity"].ToString(), out quantity))
                        {
                            if (quantity <= 0)
                            {
                                error_text += partno + " : quantity must be >0 \r\n";
                            }
                            else
                            {
                                err = checkQuantity(partno, quantity);
                                if (string.IsNullOrEmpty(err))
                                {
                                    string[] quotation = calculatePrice(partno, quantity);
                                    price = float.Parse(quotation[0]);
                                    currency = quotation[1];
                                    
                                    DataRow new_row = this.ImportTable.NewRow();
                                    new_row["OrderNo"] = order_prefix + index.ToString("D5");
                                    new_row["POID"] = this.poID.Text;
                                    new_row["PartNo"] = partno;
                                    new_row["Quantity"] = quantity;
                                    new_row["UnitID"] = unit;
                                    new_row["UnitPrice"] = price;
                                    new_row["CurrencyID"] = currency;
                                    new_row["DeliveryDate"] = excel_row["DeliveryDate"].ToString();
                                    new_row["TransportID"] = excel_row["TransportID"].ToString();
                                    new_row["DeliveryTermID"] = deliveryterm;
                                    new_row["Note"] = excel_row["Note"].ToString();
                                    new_row["Purpose"] = excel_row["Purpose"].ToString();
                                    this.ImportTable.Rows.Add(new_row);

                                    index++;
                                }
                                else
                                {
                                    error_text += partno + " : " + err + " \r\n";
                                }
                            }
                        }
                        else
                        {
                            error_text += partno + " : invalid quantity \r\n";
                        }
                    }
                }                
            }

            gridImportData.DataSource = ImportTable;
            ImportData.BestFitColumns();

            if (!string.IsNullOrEmpty(error_text))
            {
                MessageBox.Show(error_text);
                error_text = null;
            }
        }

        //////////////////////////////////////////////
        /// Create Department List
        /////////////////////////////////////////////
        private void getDepartmentList()
        {
            ComboboxItem[] item = new ComboboxItem[5];
            item[0] = new ComboboxItem();
            item[0].Text = "Production (PT)";
            item[0].Value = "PT";

            item[1] = new ComboboxItem();
            item[1].Text = "Assembly (AT)";
            item[1].Value = "AT";

            item[2] = new ComboboxItem();
            item[2].Text = "Administration (GA)";
            item[2].Value = "GA";

            item[3] = new ComboboxItem();
            item[3].Text = "Business (BT)";
            item[3].Value = "BT";

            item[4] = new ComboboxItem();
            item[4].Text = "Accounting (KT)";
            item[4].Value = "KT";

            for (int i = 0; i < 5; i++)
            {
                this.departmentID.Properties.Items.Add(item[i]);
            }
        }

        ///////////////////////////////////
        /// Check MOQ and Marume
        //////////////////////////////////
        private string checkQuantity(string part, float qty)
        {
            string ret = null;
            float moq = 0;
            float marume = 0;
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT [Marume], [MinQuantity]"
                    + " FROM [222_Part] WHERE PartNo='" + part + "'";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    if (dataReader["MinQuantity"] != DBNull.Value)
                    {
                        if (float.TryParse(dataReader["MinQuantity"].ToString(), out moq))
                        {
                            if (moq == 0) moq = 1;
                        }
                        else
                        {
                            moq = 1;
                        }
                    }
                    else
                    {
                        moq = 1;
                    }

                    if (dataReader["Marume"] != DBNull.Value)
                    {
                        if (float.TryParse(dataReader["Marume"].ToString(), out marume))
                        {
                            if (marume == 0) marume = 1;
                        }
                        else
                        {
                            marume = 1;
                        }
                    }
                    else
                    {
                        marume = 1;
                    }
                }
                dataReader.Close();
                sql_cmd.Dispose();
                myConnection.Close();

                if (qty < moq)
                {
                    ret = "Quantity must be greater than MOQ: " + moq.ToString() + "\r\n";                    
                }
                else
                {
                    if ((qty % marume) > 0)
                    {
                        ret = "Quantity must be divisible by Marume: " + marume.ToString();
                    }
                }
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ret = "Cannot check MOQ and Marume.";
            }
            return ret;
        }
        

        ////////////////////////////////////////////////
        /// Get POID List to form
        //////////////////////////////////////////////// 
        private void getPOIDList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT ID FROM [241_PO] WHERE Finished=0 ORDER BY ID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["ID"].ToString();
                    item.Value = dataReader["ID"].ToString();
                    this.poID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get POID list");
            }
        }        

        ////////////////////////////////////////////////
        /// Get PartNo List to form
        //////////////////////////////////////////////// 
        private string[] getPartNoInfo(string part)
        {   
            /////////////////////////////////////////////////////////////
            /// Get PartNo compactable with Supplier ID in Quotation
            ////////////////////////////////////////////////////////////
            //MessageBox.Show(supplier);
            string[] ret = new string[2];

            if (!string.IsNullOrEmpty(supplier))
            {
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT t1.[PartNo], t2.BuyingUnit AS UnitID"
                        + " FROM [241_ImportQuotation] AS t1 INNER JOIN"
                        + " [222_Part] AS t2 ON t1.[PartNo]=t2.[PartNo]"
                        + " WHERE t1.[SupplierID]='" + supplier + "' AND t1.[PartNo]='" + part + "'";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {
                        ret[0] = dataReader["PartNo"].ToString();
                        if (!string.IsNullOrEmpty(dataReader["UnitID"].ToString().Trim()))
                        {
                            if (((int)(dataReader["UnitID"].ToString().Trim()[0])) == 65402)
                            {
                                ret[1] = "PC";
                            }
                            else
                            {
                                ret[1] = dataReader["UnitID"].ToString().Trim();
                            }
                        }
                        else
                        {
                            ret[1] = dataReader["UnitID"].ToString().Trim();
                        }
                        
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot get PartNo list");
                }
            }
            return ret;
        }


        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            if (this.ImportTable.Rows.Count > 0)
            {
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd;
                    foreach (DataRow add_row in ImportTable.Rows)
                    {
                        sql_cmd = myConnection.CreateCommand();
                        sql_cmd.CommandText = "INSERT INTO [241_PODetails]"
                        + " (POID, OrderNo, PartNo, Quantity, UnitID, UnitPrice, CurrencyID, DeliveryDate, TransportID,"
                        + " DeliveryTermID, Note, Purpose) VALUES ('" + add_row["POID"].ToString() + "',"
                        + " '" + add_row["OrderNo"].ToString() + "', N'" + add_row["PartNo"].ToString() + "',"
                        + " " + add_row["Quantity"].ToString() + ", '" + add_row["UnitID"].ToString() + "',"
                        + " " + add_row["UnitPrice"].ToString() + ", '" + add_row["CurrencyID"].ToString() + "',"
                        + " @deliverydate, '" + add_row["TransportID"] + "', '" + add_row["DeliveryTermID"].ToString() + "',"
                        + " '" + add_row["Note"].ToString() + "', '" + add_row["Purpose"].ToString() + "')";
                        DateTime deliverydate = new DateTime();
                        if (DateTime.TryParse(add_row["DeliveryDate"].ToString(), out deliverydate))
                        {
                            sql_cmd.Parameters.Add("@deliverydate", SqlDbType.DateTime).Value = deliverydate.Date;
                        }
                        else
                        {
                            sql_cmd.Parameters.Add("@deliverydate", SqlDbType.DateTime).Value = DBNull.Value;
                        }                        
                        sql_cmd.ExecuteNonQuery();
                        sql_cmd.Dispose();
                    }
                    myConnection.Close();
                    MessageBox.Show("Data has been imported succeedfully.");
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                }

                this.ImportTable.Clear();
            }
        }

        private void poID_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.poID.SelectedIndex >= 0)
            {
                ComboboxItem poid = (ComboboxItem)poID.SelectedItem;
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT SupplierID, PaymentID, DeliveryTermID"
                        + " FROM [241_PO] WHERE ID='" + poid.Value.ToString() + "'";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {
                        supplier = dataReader["SupplierID"].ToString();
                        payment = dataReader["PaymentID"].ToString();
                        deliveryterm = dataReader["DeliveryTermID"].ToString();
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    supplier = null;
                    payment = null;
                    deliveryterm = null; 
                }                                
            }
        }
               
        //////////////////////////////////////////////
        /// Calcaulate Price with PartNo and Quantity
        //////////////////////////////////////////////
        private string[] calculatePrice(string part, float qty)
        {
            string[] ret = new string[2];
            ret[0] = "0";
            ret[1] = "USD";
            if (payment == "NoComercia")
            {
                ret[0] = "0";
                ret[1] = "USD";
            }
            else
            {
                float max_qty = 0;
                bool pass = true;
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT Quantity"
                        + " FROM [241_ImportQuotation] WHERE PartNo='" + part + "' AND SupplierID='" + supplier + "'"
                        + " AND Quantity < 99999"
                        + " ORDER BY Quantity DESC";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {
                        max_qty = float.Parse(dataReader["Quantity"].ToString());                        
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    pass = false;
                }

                if (pass)
                {
                    if (qty < max_qty)
                    {
                        try
                        {
                            myConnection.Open();
                            SqlCommand cmd_sql = myConnection.CreateCommand();
                            cmd_sql.CommandText = "SELECT Price, CurrencyID"
                                + " FROM [241_ImportQuotation] WHERE PartNo='" + part + "'"
                                + " AND SupplierID='" + supplier + "' AND Quantity<=" + qty
                                + " ORDER BY Price";
                            SqlDataReader dataReader = cmd_sql.ExecuteReader();
                            if (dataReader.Read())
                            {
                                ret[1] = dataReader["CurrencyID"].ToString();                                
                                ret[0] = dataReader["Price"].ToString();
                            }
                            dataReader.Close();
                            cmd_sql.Dispose();
                            myConnection.Close();
                        }
                        catch
                        {
                            if (myConnection.State == ConnectionState.Open)
                            {
                                myConnection.Close();
                            }
                            MessageBox.Show("Cannot get price for this PartNo.");
                        }
                    }
                    else
                    {
                        try
                        {
                            myConnection.Open();
                            SqlCommand cmd_sql = myConnection.CreateCommand();
                            cmd_sql.CommandText = "SELECT Price, CurrencyID"
                                + " FROM [241_ImportQuotation] WHERE PartNo='" + part + "'"
                                + " AND SupplierID='" + supplier + "'"
                                + " ORDER BY Price";
                            SqlDataReader dataReader = cmd_sql.ExecuteReader();
                            if (dataReader.Read())
                            {
                                ret[1] = dataReader["CurrencyID"].ToString();
                                ret[0] = dataReader["Price"].ToString();
                            }
                            dataReader.Close();
                            cmd_sql.Dispose();
                            myConnection.Close();
                        }
                        catch
                        {
                            if (myConnection.State == ConnectionState.Open)
                            {
                                myConnection.Close();
                            }
                            MessageBox.Show("Cannot get price for this PartNo.");
                        }
                    }
                }
                else
                {
                    MessageBox.Show("Cannot get price for this PartNo.");
                }
            }
            return ret;
        }

        private void departmentID_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.departmentID.SelectedIndex >= 0)
            {
                order_prefix = null;
                index = 0;
                ComboboxItem item = (ComboboxItem)this.departmentID.SelectedItem;
                order_prefix = item.Value.ToString();
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT MAX(SUBSTRING(OrderNo, 3, LEN(OrderNo))) FROM [241_PODetails]"
                        + " WHERE SUBSTRING(OrderNo, 1, 2) = '" + order_prefix + "'";
                    SqlDataReader dataReader = sql_cmd.ExecuteReader();
                    if (dataReader.Read())
                    {
                        if (dataReader[0] != DBNull.Value)
                        {
                            Int32.TryParse(dataReader[0].ToString(), out index);
                        }
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();
                    myConnection.Close();

                    index++;                    
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show("Cannot create OrderNo");
                    //MessageBox.Show(ex.Message);
                }
            }
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            if (this.poID.SelectedIndex < 0)
            {
                MessageBox.Show("Please select POID");
            }
            else
            {
                if (this.departmentID.SelectedIndex < 0)
                {
                    MessageBox.Show("Please select Department");
                }
                else
                {
                    ImportExcel();
                }
            }
        }
    }
}
